#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2022
# @Author  : lingfeng.hu@nokia-sbell.com)
# @Link    : TBD
# @Version : $Id$

# https://jiradc.ext.net.nokia.com/rest/api/2/issue/FPB-662472
import os
from shutil import copy
import shutil
from atlassian import Jira
from openpyxl import Workbook, load_workbook
from datetime import datetime
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.axis import DateAxis
from FeatureBuildsSchedule import buildFeatureBuildsTimeline, getFeatureBuild, getFeatureBuildByFbId
from dateutil.relativedelta import relativedelta

nonWorkingDays = {
                  '2201': ['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-08', '2022-01-09', '2022-01-15', '2022-01-16'],
                  '2202': ['2022-01-22', '2022-01-23', '2022-01-31'],
                  '2203': ['2022-02-01', '2022-02-02', '2022-02-03', '2022-02-04', '2022-02-05', '2022-02-06', '2022-02-12', '2022-02-13'],
                  '2204': ['2022-02-19', '2022-02-20', '2022-02-26', '2022-02-27'],
                  '2205': ['2022-03-05', '2022-03-06', '2022-03-12', '2022-03-13'],
                  '2206': ['2022-03-19', '2022-03-20', '2022-03-26', '2022-03-27'],
                  '2207': ['2022-04-03', '2022-04-04', '2022-04-05','2022-04-09', '2022-04-10'],
                  '2208': ['2022-04-16', '2022-04-17', '2022-04-23'],
                  '2209': ['2022-04-30', '2022-05-01', '2022-05-02', '2022-05-03', '2022-05-04','2022-05-08'],
                  '2210': ['2022-05-14', '2022-05-15', '2022-05-21', '2022-05-22'],
                  '2211': ['2022-05-28', '2022-05-29', '2022-06-03', '2022-06-04', '2022-06-05'],
                  '2212': ['2022-06-11', '2022-06-12', '2022-06-18', '2022-06-19'],
                  '2213': ['2022-06-25', '2022-06-26', '2022-07-02', '2022-07-03'],
                  '2214': ['2022-07-09', '2022-07-10', '2022-07-16', '2022-07-17'],
                  '2215': ['2022-07-23', '2022-07-24', '2022-07-30', '2022-07-31'],
                  '2216': ['2022-08-06', '2022-08-07', '2022-08-13', '2022-08-14'],
                  '2217': ['2022-08-20', '2022-08-21', '2022-08-27', '2022-08-28'],
                  '2218': ['2022-09-10', '2022-09-11', '2022-09-12'],
                  '2219': ['2022-09-17', '2022-09-18', '2022-09-24', '2022-09-25'],
                  '2220': ['2022-10-01', '2022-10-02', '2022-10-03', '2022-10-04', '2022-10-05', '2022-10-06', '2022-10-07'],
                  '2221': ['2022-10-15', '2022-10-16', '2022-10-22', '2022-10-23'],
                  '2222': ['2022-10-29', '2022-10-30', '2022-11-05', '2022-11-06'],
                  '2223': ['2022-11-12', '2022-11-13', '2022-11-19', '2022-11-20'],
                  '2224': ['2022-11-26', '2022-11-27', '2022-12-03', '2022-12-04'],
                  '2225': ['2022-12-10', '2022-12-11', '2022-12-17', '2022-12-18'],
                  '2226': ['2022-12-24', '2022-12-25', '2022-12-31', '2023-01-01', '2023-01-02']
                }

teamMapping = {
##############################################################################
#     jiraTeamId: jiraTeamName, tribe_line, activity, SGL, LPO, tribe        #
##############################################################################

###############################################################################
#                     CN1                                                     #
###############################################################################
    "6389" : ("L3_5GL3EFS_HZH_CPEFS", "EFS", "EFS", "Fu Johnny", "Tao Tommy", "CN1_EFS"),
   
    "5219" : ("L3_5GL3_SG01_HZH_ACE", "SG01", "SW", "Xu Feng", "Shen Yuanfeng", "CN1_SW"),
    "5220" : ("L3_5GL3_SG01_HZH_Carry", "SG01", "SW", "Xu Feng", "Shen Yuanfeng", "CN1_SW"),
    
    "6522" : ("L3_5GL3_SG02_HZH_KongFu", "SG02", "SW", "Wei Lianggang", "Zhong Hai", "CN1_SW"),
    "6801" : ("L3_5GL3_SG02_HZH_MAMBA", "SG02", "SW", "Wei Lianggang", "Zhong Hai", "CN1_SW"),
    
    "5222" : ("L3_5GL3_SG03_HZH_Eagle", "SG03", "SW", "Xu Huabing", "Zhang Alice", "CN1_SW"),
    "5224" : ("L3_5GL3_SG03_HZH_Trident", "SG03", "SW", "Xu Huabing", "Zhang Alice", "CN1_SW"),
    "5225" : ("L3_5GL3_SG03_HZH_Venus", "SG03", "SW", "Xu Huabing", "Zhang Alice", "CN1_SW"),
    
    "5226" : ("L3_5GL3_SG04_HZH_Dragonhawk", "SG04", "SW", "Ni Chen", "Zhang Jun", "CN1_SW"),
    "5227" : ("L3_5GL3_SG04_HZH_Griffin", "SG04", "SW", "Ni Chen", "Zhang Jun", "CN1_SW"),
    
    "5250" : ("L3_5GL3_SG05_HZH_Blink", "SG05", "SW", "Zhang Xi", "Tao Tommy", "CN1_SW"),
    "6391" : ("L3_5GL3_SG05_HZH_Easy", "SG05", "SW", "Zhang Xi", "Tao Tommy", "CN1_SW"),
    "8514" : ("L3_5GL3_SG05_HZH_Frame", "SG05", "SW", "Zhang Xi", "Tao Tommy", "CN1_SW"),

    "5233" : ("L3_5GL3_SG08_HZH_BARRETT", "SG08", "SW", "Yang Janet", "Ma Jun", "CN1_SW"),
    "8536" : ("L3_5GL3_SG08_HZH_Center", "SG08", "SW", "Yang Janet", "Ma Jun", "CN1_SW"),
    "5234" : ("L3_5GL3_SG08_HZH_BEE", "SG08", "SW", "Yang Janet", "Ma Jun", "CN1_SW"),
    "8050" : ("L3_5GL3_SG08_HZH_PER", "SG08", "SW", "Yang Janet", "Hu Lingfeng", "CN1_SW"),

    "5235" : ("L3_5GL3_SG09_SHA_Groovyard", "SG09", "SW", "Huang Linqian", "Du Jianjun", "CN1_SW"),
    "5236" : ("L3_5GL3_SG09_SHA_Typhoon", "SG09", "SW", "Huang Linqian", "Du Jianjun", "CN1_SW"),
    "5237" : ("L3_5GL3_SG09_SHA_Weeasy", "SG09", "SW", "Huang Linqian", "Du Jianjun", "CN1_SW"),

    "5241" : ("L3_5GL3_SG11_SHA_ROAD", "SG11", "SW", "Ling Yun", "Xiong Wenjie", "CN1_SW"),

    "5243" : ("L3_5GL3_SG12_HZH_GELIVABLE", "SG12", "SW", "Yan Erin", "Wang Dong", "CN1_SW"),

    "6262" : ("L3_5GL3_SG14_HZH_DRAGONBALL", "SG14", "SW", "Liu Jingya", "Ma Jun", "CN1_SW"),
    "8806" : ("L3_5GL3_SG14_HZH_DRAGONFLY", "SG14", "SW", "Liu Jingya", "Ma Jun", "CN1_SW"),
###############################################################################
#                     CN2                                                     #
###############################################################################
    "8580" : ("L3_5GL3EFS_NJG_Venus", "EFS", "EFS", "Wu Chao", "Wu Chao", "CN2_EFS"),

    "7955" : ("L3_5GL3_NJG_Mars", "CN2", "SW", "Wu Chao", "Wu Chao", "CN2_SW"),
    "7962" : ("L3_5GL3_NJG_Nemo", "CN2", "SW", "Wu Chao", "Wu Chao", "CN2_SW"),
    "8469" : ("L3_5GL3_NJG_Mercury", "CN2", "SW", "Wu Chao", "Wu Chao", "CN2_SW"),
    "8482" : ("L3_5GL3_NJG_Jupiter", "CN2", "SW", "Wu Chao", "Wu Chao", "CN2_SW"),
    "8735" : ("L3_5GL3_NJG_Saturn", "CN2", "SW", "Wu Chao", "Wu Chao", "CN2_SW"),

###############################################################################
#                     CN3                                                     #
###############################################################################
    "8044" : ("L3_5G L3 EFS_HZ64_NorthStar", "EFS", "EFS", "Ding Wenyao", "Ding Wenyao", "CN3_EFS"),

    "6624" : ("L3_5G L3_HZ64_FTH1", "SG01", "SW", "Chen Weidong", "Chen Weidong", "CN3_SW"),
    "8619" : ("L3_5G L3_HZ64_FTH11", "SG01", "SW", "Chen Weidong", "Chen Weidong", "CN3_SW"),

    "8038" : ("L3_5G L3_HZ64_FTH5", "SG03", "SW", "Chen Jiajun", "Huang Hengkuai", "CN3_SW"),

    "5228" : ("L3_5G L3_HZ64_STAR", "SG05", "SW", "Shi Yingchao", "Tang Liping", "CN3_SW"),
    "5231" : ("L3_5G L3_HZ64_GAEA", "SG05", "SW", "Shi Yingchao", "Pan Jun", "CN3_SW"),

    "5223" : ("L3_5G L3_HZ64_Ocean", "SG06", "SW", "Xian Bo", "Hu Zhifeng", "CN3_SW"),
    "5229" : ("L3_5G L3_HZ64_BINGO", "SG06", "SW", "Xian Bo", "Hu Zhifeng", "CN3_SW"),

    "8042" : ("L3_5G L3_HZ64_FTH25", "SG07", "SW", "He Zhenjie", "Cheng Weikun", "CN3_SW"),
    "8470" : ("L3_5G L3_HZ64_TrunkForce", "SG07", "SW", "He Zhenjie", "Cheng Weikun", "CN3_SW"),
    "8731" : ("L3_5G L3_HZ64_SCMCI", "SG07", "SW", "He Zhenjie", "Wang Lingming", "CN3_SW"),
    
    "8041" : ("L3_5G L3_HZ64_FTH24", "SG08", "SW", "Niu Xuezheng", "Gong Xiaofeng", "CN3_SW"),
    
    "8039" : ("L3_5G L3_HZ64_FTH21", "SG09", "SW", "Wu Wandeng", "Tang Liping", "CN3_SW"),
    "8040" : ("L3_5G L3_HZ64_FTH22", "SG09", "SW", "Wu Wandeng", "Pan Jun", "CN3_SW"),
    
    "8376" : ("L3_5G L3_HZ64_FTH99", "A&S", "SW", "Yao Yuheng", "Ding Wenyao", "CN3_SW"),
    
###############################################################################
#                     DCM                                                     #
###############################################################################
    "8182" : ("DCM_5G_L3_EFS_BJ_JobRotation", "EFS", "EFS", "Yuan Dl", "Yuan Dl", "DCM_EFS"),
    "8183" : ("DCM_5G_L3_BJ_JobRotation", "SW", "SW", "Yuan Dl", "Yuan Dl", "DCM_SW")
}

def getItemId(issue):
    return issue['fields']['customfield_38702'] if issue['fields']['customfield_38702'] else ''

def getKey(issue):
    return issue['key'] if issue['key'] else ''

def getSummary(issue):
    return issue['fields']['summary'] if issue['fields']['summary'] else ''

def getPlannedSystemRelease(issue):
    try:
        return issue['fields']['customfield_38724'][0]['value']
    except:
        return ''

def getBusinessPrio1(issue):
    return issue['fields']['customfield_38716'] if issue['fields']['customfield_38716'] else ''

def getAssignee(issue):
    return issue['fields']['assignee']['displayName'] if issue['fields']['assignee'] else ''

def getStatus(issue):
    return issue['fields']['status']['name'] if issue['fields']['status'] else ''

def getTeam(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return getLineId(issue) + '_' + teamMapping[issue['fields']['customfield_29790']][0].split('_')[-1]
        else:
            return issue['fields']['customfield_29790']
    else: 
        return ''

def getLineId(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return getTribe(issue) + '_' + teamMapping[issue['fields']['customfield_29790']][1]
        else:
            return ''
    else: 
        return ''

def getActivityType(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return teamMapping[issue['fields']['customfield_29790']][2]
        else:
            return ''
    else: 
        return ''

def getSglId(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return teamMapping[issue['fields']['customfield_29790']][3]
        else:
            return ''
    else: 
        return ''

def getLPO(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return teamMapping[issue['fields']['customfield_29790']][4]
        else:
            return ''
    else:
        return ''

def getTribe(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return teamMapping[issue['fields']['customfield_29790']][5]
        else:
            return ''
    else: 
        return ''

def getLabels(issue):
    return ','.join(issue['fields']['labels'])

def getTimeRemaining(issue):
    return issue['fields']['customfield_43291'] if issue['fields']['customfield_43291'] else 0

def getTimeLogged(issue):
    return issue['fields']['customfield_43290'] if issue['fields']['customfield_43290'] else 0

def getOriginalEstimate(issue):
    return issue['fields']['customfield_43292'] if issue['fields']['customfield_43292'] else 0

def getStartFb(issue):
    try:
        return int(issue['fields']['customfield_38694'])
    except:
        return 0

def getEndFb(issue):
    try:
        return int(issue['fields']['customfield_38693'])
    except:
        return 0

def getFbCommitStatus(issue):
    return issue['fields']['customfield_38727']

def getStretchReason(issue):
    return issue['fields']['customfield_38727']

def getDelayReason(issue):
    return issue['fields']['customfield_38759'][0]['value'] if issue['fields']['customfield_38759'] else ''

def getDelayExplanation(issue):
    return issue['fields']['customfield_38755']

def getRfcFB(issue):
    try:
        return int(issue['fields']['customfield_43490']['value'])
    except:
        return 0

def getRfcStatus(issue):
    return issue['fields']['customfield_38728']['value'] if issue['fields']['customfield_38728'] else ''

def getCompetenceArea(issue):
    return issue['fields']['customfield_38690']['value'] if issue['fields']['customfield_38690'] else ''

def getFs0Ee(issue):
    return issue['fields']['customfield_38737'] if issue['fields']['customfield_38737'] else ''

def getFs2Ee(issue):
    return issue['fields']['customfield_38692'] if issue['fields']['customfield_38692'] else ''

def getFS2Status(issue):
    return issue['fields']['customfield_38705'] if issue['fields']['customfield_38705'] else ''

def getFotLeader(issue):
    return issue['fields']['customfield_38695']['displayName'] if issue['fields']['customfield_38695'] else ''

def getWorkType(issue):
    return issue['fields']['customfield_38368']['value'] if issue['fields']['customfield_38368'] else ''

def getIssueType(issue):
    return issue['fields']['issuetype']['name'] if issue['fields']['issuetype'] else ''

def getFeatureId(issue):
    return issue['fields']['customfield_37381'] if issue['fields']['customfield_37381'] else ''

def getReqArea(issue):
    return issue['fields']['customfield_38712'] if issue['fields']['customfield_38712'] else ''

def getActType(issue):
    return issue['fields']['customfield_38750']['value'] if issue['fields']['customfield_38750'] else ''

def getText2(issue):
    return issue['fields']['customfield_38727'] if issue['fields']['customfield_38727'] else ''

def getSpecEndFB(issue):
    return issue['fields']['customfield_38739'] if issue['fields']['customfield_38739'] else ''

def getCustomer(issue):
    customerList = []
    if issue['fields']['customfield_32006']:
        for customer in issue['fields']['customfield_38693']:
            customerList.append(customer['value'])
        return ','.join(customerList)
    else:
        return ''

def getTargetReleases(issue):
    try:
        return issue['fields']['customfield_38710']
    except AttributeError:
        return ''

def getCreatedTime(issue):
    return issue['fields']['created'] if issue['fields']['created'] else ''

def getUpdatedTime(issue):
    return issue['fields']['updated'] if issue['fields']['updated'] else ''

def getResolvedTime(issue):
    return issue['fields']['resolutiondate'] if issue['fields']['resolutiondate'] else ''

# jirashell -s https://jiradc.ext.net.nokia.com -u <user> -p <passwd>
# issue=jira.issue('FPB-263466')
# issue.fields.customfield_38759[0].value

def getIssues(jqlRequest):
    now = datetime.now()
    print("fetch {}-{}".format('0', nokiaJiraDcResultLimitNum))
    issuesJira = jira.jql(jqlRequest, jqlFields, start=0, limit=nokiaJiraDcResultLimitNum)
    print("spend time: {} in this round".format(datetime.now() - now))
    # print('=================================')
    # print(len(issuesJira['issues']))
    # for issue in issuesJira['issues']:
    #     print(issue['key'])
    
    totalIssuesNum = issuesJira['total']
    print("total: {}".format(totalIssuesNum))

    # totalIssuesNum = issuesJira['total']
    if int(nokiaJiraDcResultLimitNum) < totalIssuesNum:
        continueRoundCount = totalIssuesNum//int(nokiaJiraDcResultLimitNum)
        for round in range(continueRoundCount):
            startIndex = int(nokiaJiraDcResultLimitNum) * (round+1)
            print("fetch next: {}-{}".format(startIndex, startIndex+int(nokiaJiraDcResultLimitNum)-1))
            startTime = datetime.now()
            anothrIssuesJira = jira.jql(jqlRequest, jqlFields, start=startIndex, limit=nokiaJiraDcResultLimitNum)
            issuesJira['issues'].extend(jira.jql(jqlRequest, jqlFields, start=startIndex, limit=nokiaJiraDcResultLimitNum)['issues'])
            print("spend time: {} in this round".format(datetime.now() - startTime))
    
    return issuesJira['issues']

nokiaJiraDcResultLimitNum = 1000

jqlFeatureFieldsMapping = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Planned System Release', 'customfield_38724'),
        ('Business Priority 1', 'customfield_38716'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Team', 'customfield_29790'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('FB Commit Status', 'customfield_38758'),
        ('Stretch Goal Reason', 'customfield_43893'),
        ('Delay Reason', 'customfield_38759'),
        ('Delay Explanation', 'customfield_38755'),
        ('Release committed FB', 'customfield_43490'),
        ('Release Committed Status', 'customfield_38728'),
        ('Competence Area', 'customfield_38690'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Requirement Area', 'customfield_38712'),
        ('Activity Type', 'customfield_38750'),
        ('Text 2', 'customfield_38727')
        ]

workingDir = r'C:\Users\linhu\OneDrive - Nokia\Documents\workspace\pythonCoding\5g_cp_utils\Planning\FusionTools'
os.chdir(workingDir)
today = datetime.now().strftime("%Y-%m-%d")
startTime = datetime.now()
# xxx is windows account, yyy is password
userName = 'xxx'
passWord = 'yyy'
jira = Jira(url='https://jiradc.ext.net.nokia.com', username=userName, password=passWord)
featureBuildSchedule = buildFeatureBuildsTimeline()
fbId = getFeatureBuild(datetime.now().strftime('%Y-%m-%d'), featureBuildSchedule).getFB()
# fbId = '2205'
# fbStartDate = getFeatureBuild(datetime.now().strftime('%Y-%m-%d'), featureBuildSchedule).getStartDate()
# fbEndDate = getFeatureBuild(datetime.now().strftime('%Y-%m-%d'), featureBuildSchedule).getEndDate()
fbStartDate = getFeatureBuildByFbId(fbId, featureBuildSchedule).getStartDate()
fbEndDate = getFeatureBuildByFbId(fbId, featureBuildSchedule).getEndDate()
teamIdList = []
for team in teamMapping.keys():
    teamIdList.append(team)

jqlTeams = ''
for team in teamIdList:
    jqlTeams += team
    jqlTeams += ','
jqlTeams = jqlTeams[:-1]

teamWd = 'FeatureTeam'
jqlRequest = 'cf[29790] ' + 'in' + ' ' + '(' + jqlTeams + ')' + 'and' + ' ' + 'type=Epic' + ' ' + 'and' + ' ' + '"End FB"' + '~' + fbId
# print(jqlRequest)
jqlFields = [fieldMapping[1] for fieldMapping in jqlFeatureFieldsMapping]
# issuesFeature = getIssues(jqlRequest)
# print(len(issuesFeature))

wb = Workbook()
ws_backlog_CA = wb.active
ws_backlog_CA.title = "Backlog_All"
if not os.path.exists(teamWd):
    os.mkdir(teamWd)
teamFbBurndownOutputFile = os.path.join(teamWd, 'team' + '_' + 'FB' + fbId + '_' + 'burndown' + '.xlsx')
if os.path.exists(teamFbBurndownOutputFile):
    wbTeamFbBurndown = load_workbook(filename=teamFbBurndownOutputFile)
else:
    wbTeamFbBurndown = Workbook()
    wbTeamFbBurndown.remove(wbTeamFbBurndown.active)

def exportCAIssueToExcel(ws, issue, rowIndex, columnIndex):
        # print(issue['key'])
        ws.cell(row=rowIndex, column=columnIndex).value = getItemId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getStartFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getEndFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getFbCommitStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getStretchReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+16).value = getDelayReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+17).value = getDelayExplanation(issue)
        ws.cell(row=rowIndex, column=columnIndex+18).value = getRfcFB(issue)
        ws.cell(row=rowIndex, column=columnIndex+19).value = getRfcStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+20).value = getCompetenceArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+21).value = getFs0Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+22).value = getFs2Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+23).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+24).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+25).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+26).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+27).value = getReqArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+28).value = getActType(issue)
        ws.cell(row=rowIndex, column=columnIndex+29).value = getText2(issue)

class ChangeLog:
    def __init__(self, date, remainEE, accumulatedLogEE):
        self.date = date
        self.remainEE = remainEE
        self.accumulatedLogEE = accumulatedLogEE
        
class Epic:
    def __init__(self):
        self.name = ''
        self.line = ''
        self.sgl = ''
        self.activityType = ''
        self.tribe = ''
        self.remainEE = 0
        self.logEE = 0
        self.endFB = 0
        self.startFB = 9999
        self.status = ''
        self.changeLog = {}
        self.key = ''

    def setName(self, name):
        self.name = name
    
    def setLine(self, line):
        self.line = line

    def setSgl(self, sgl):
        self.sgl = sgl

    def setActivityType(self, activityType):
        self.activityType = activityType

    def setTribe(self, tribe):
        self.tribe = tribe

    def setLogEE(self, logEE):
        self.logEE = logEE
    
    def setRemainEE(self, remainEE):
        self.remainEE = int(remainEE)

    def setEndFB(self, endFB):
        self.endFB = endFB
    
    def setStartFB(self, startFB):
        self.startFB = startFB

    def setStatus(self, status):
        self.status = status
    
    def updateChangeLog(self, changeLog):
        self.changeLog[changeLog.date] = changeLog

    def setKey(self, key):
        self.key = key
    
    def getKey(self):
        return self.key

    def getActivityType(self):
        return self.activityType

    def getTribe(self):
        return self.tribe

class TeamIssues:
    def __init__(self, teamId):
        self.teamId = teamId
        self.issues = []
    
    def getRemainEeByDate(self, date):
        totalAllRemainEe = 0
        totalSWRemainEe = 0
        totalEFSRemainEe = 0
        for issue in self.issues:
            # print('Key: {}'.format(issue.getKey()))
            activityType = issue.getActivityType()
            oneIssueRemainEe = 0
            for createdDate in sorted(issue.changeLog):
                # print(createdDate)
                if createdDate <= date:
                    oneIssueRemainEe = issue.changeLog[createdDate].remainEE
                    # print(oneIssueRemainEe)
                else:
                    break
                    # continue
            totalAllRemainEe += oneIssueRemainEe
            if activityType == 'SW':
                totalSWRemainEe += oneIssueRemainEe
            elif activityType == 'EFS':
                totalEFSRemainEe += oneIssueRemainEe
                    
        return (totalAllRemainEe, totalSWRemainEe, totalEFSRemainEe)
    
    def getLogEeByDate(self, date):
        totalAllLogEe = 0
        totalSWLogEe = 0
        totalEFSLogEe = 0
        for issue in self.issues:
            # print('Key: {}'.format(issue.getKey()))
            activityType = issue.getActivityType()
            oneIssueLogEe = 0
            oneIssueAccumualtedLogEe = 0
            for createdDate in sorted(issue.changeLog):
                # print('getLogEeByDate', createdDate)
                if createdDate < date:
                    oneIssueAccumualtedLogEe = issue.changeLog[createdDate].accumulatedLogEE
                    # print('getLogEeByDate oneIssueAccumualtedLogEe', oneIssueAccumualtedLogEe)
                elif createdDate == date:
                    oneIssueLogEe = issue.changeLog[createdDate].accumulatedLogEE - oneIssueAccumualtedLogEe
                    # print('getLogEeByDate oneIssueAccumualtedLogEe', oneIssueAccumualtedLogEe)
                    # print('getLogEeByDate oneIssueLogEe', oneIssueLogEe)
                    break
                    # continue
            totalAllLogEe += oneIssueLogEe
            if activityType == 'SW':
                totalSWLogEe += oneIssueLogEe
            elif activityType == 'EFS':
                totalEFSLogEe += oneIssueLogEe
        
        return (totalAllLogEe, totalSWLogEe, totalEFSLogEe)

    def getAccumulatedLogEeByDate(self, date):
        totalAllAccumulatedLogEe = 0
        totalSWAccumulatedLogEe = 0
        totalEFSAccumulatedLogEe = 0
        for issue in self.issues:
            activityType = issue.getActivityType()
            oneIssueAccumulatedLogEe = 0
            for createdDate in sorted(issue.changeLog):
                if createdDate <= date:
                    oneIssueAccumulatedLogEe = issue.changeLog[createdDate].accumulatedLogEE
                    # print('getLogEeByDate oneIssueAccumualtedLogEe', oneIssueAccumulatedLogEe)
                else:
                    break
            totalAllAccumulatedLogEe += oneIssueAccumulatedLogEe
            if activityType == 'SW':
                totalSWAccumulatedLogEe += oneIssueAccumulatedLogEe
            elif activityType == 'EFS':
                totalEFSAccumulatedLogEe += oneIssueAccumulatedLogEe

        return (totalAllAccumulatedLogEe, totalSWAccumulatedLogEe, totalEFSAccumulatedLogEe)

    def addIssue(self, issue):
        self.issues.append(issue)

    def getIssues(self):
        return self.issues

def buildEpicList(issues):
    teamIssuesContainer = {}
    for issue in issues:
        teamId = getTeam(issue)
        sglId = getSglId(issue)
        lineId = getLineId(issue) + '_' + sglId
        activityType = getActivityType(issue)
        tribe = getTribe(issue)
        timeRemainEE = getTimeRemaining(issue)
        timeLogEE = getTimeLogged(issue)
        startFB = getStartFb(issue)
        endFB = getEndFb(issue)
        status = getStatus(issue)
        key = getKey(issue)

        oneEpic = Epic()
        oneEpic.setName(teamId)
        oneEpic.setLine(lineId)
        oneEpic.setSgl(sglId)
        oneEpic.setActivityType(activityType)
        oneEpic.setTribe(tribe)
        oneEpic.setRemainEE(timeRemainEE)
        oneEpic.setLogEE(timeLogEE)
        oneEpic.setStartFB(startFB)
        oneEpic.setEndFB(endFB)
        oneEpic.setStatus(status)
        oneEpic.setKey(key)
    
        # print('team: {}, Key : {}'.format(teamId, getKey(issue)))
        changeLog = jira.get_issue_changelog(issue_key=getKey(issue))
        remainEE = 0
        accumulatedLogEE = 0
        for history in changeLog['histories']:
            for item in history['items']:
                createdTime = datetime.strptime(history['created'], '%Y-%m-%dT%H:%M:%S.%f%z').astimezone().strftime('%Y-%m-%d')
                if item['field'] == 'Σ Time Remaining (h)':
                    # createdTime = datetime.strptime(history['created'], '%Y-%m-%dT%H:%M:%S.%f%z').astimezone().strftime('%Y-%m-%d')
                    remainEE = float(item['toString'])
                    # print(createdTime)
                    # print('remainEE: {}'.format(remainEE))
                if item['field'] == 'Σ Logged Effort (h)':
                    accumulatedLogEE = float(item['toString'])
                    # print(createdTime)
                    # print('accumlatedLogEE: {}'.format(accumulatedLogEE))
                oneChangeLog = ChangeLog(date=createdTime, remainEE=remainEE, accumulatedLogEE=accumulatedLogEE)
                oneEpic.updateChangeLog(oneChangeLog)
        
        if teamId not in teamIssuesContainer.keys():
            oneTeamIssues = TeamIssues(teamId)
            oneTeamIssues.addIssue(oneEpic)
            teamIssuesContainer[teamId] = oneTeamIssues
            
            if lineId not in teamIssuesContainer.keys():
                oneLineIssues = TeamIssues(lineId)
                oneLineIssues.addIssue(oneEpic)
                teamIssuesContainer[lineId] = oneLineIssues
            else:
                teamIssuesContainer[lineId].addIssue(oneEpic)

            if tribe not in teamIssuesContainer.keys():
                oneTribeIssues = TeamIssues(tribe)
                oneTribeIssues.addIssue(oneEpic)
                teamIssuesContainer[tribe] = oneTribeIssues
            else:
                teamIssuesContainer[tribe].addIssue(oneEpic)
        else:
            teamIssuesContainer[teamId].addIssue(oneEpic)
            teamIssuesContainer[lineId].addIssue(oneEpic)
            teamIssuesContainer[tribe].addIssue(oneEpic)

    return teamIssuesContainer
    
    # return set(filter(None, caList))

def genBacklogCAOutput(ws):
    print("Fetch backlogCA")
    backlog_CaHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Planned System Release', 'customfield_38724'),
        ('Business Priority 1', 'customfield_38716'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Team', 'customfield_29790'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('FB Commit Status', 'customfield_38758'),
        ('Stretch Goal Reason', 'customfield_43893'),
        ('Delay Reason', 'customfield_38759'),
        ('Delay Explanation', 'customfield_38755'),
        ('Release committed FB', 'customfield_43490'),
        ('Release Committed Status', 'customfield_38728'),
        ('Competence Area', 'customfield_38690'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Requirement Area', 'customfield_38712'),
        ('Activity Type', 'customfield_38750'),
        ('Text 2', 'customfield_38727')
        ]
    columnIndex = 1
    for header in backlog_CaHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    issues = getIssues(jqlRequest)
    for issue in issues:
        exportCAIssueToExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)
    
    return issues

def removeDuplicateRecordByDate(date, ws):
    dateColumn = 1
    for i in range(1, ws.max_column+1):
        if ws.cell(row=1, column=i).value == 'Date':
            dateColumn = i
            break
    for row in range(1, ws.max_row+1):
        if ws.cell(row=row, column=dateColumn).value == date:
            ws.delete_rows(row)
            # print('=======removeDuplicateRecordByDate=======')

def removeDuplicateRecordByWeek(wk, ws):
    weekColumn = 1
    for i in range(1, ws.max_column+1):
        if ws.cell(row=1, column=i).value == 'Week':
            weekColumn = i
            break
    for row in range(1, ws.max_row+1):
        if ws.cell(row=row, column=weekColumn).value == wk:
            ws.delete_rows(row)
            # print('=======removeDuplicateRecordByWeek=======')

def getWeekNumber(date):
    return datetime.strptime(date, '%Y-%m-%d').isocalendar()[1]

def buildTeamBurndownChart(teamName, view, startDate, endDate, today, ws):
    ws.delete_rows(1, ws.max_row)
    rowIndex = 1
    fbColumn = 1
    weekColumn = fbColumn + 1
    dateColumn = weekColumn + 1
    remainEEColumn = dateColumn + 1
    accumulatedLogEEColumn = remainEEColumn + 1
    logEEColumn = accumulatedLogEEColumn + 1
    idealRemainEEColumn = logEEColumn + 1

    if ws.cell(row=1, column=1).value == 'FB' and ws.cell(row=1, column=2).value != '':
        rowIndex = ws.max_row + 1
    else:
        ws.cell(row=rowIndex, column=fbColumn).value = 'FB'
        ws.cell(row=rowIndex, column=weekColumn).value = 'Week'
        ws.cell(row=rowIndex, column=dateColumn).value = 'Date'
        ws.cell(row=rowIndex, column=remainEEColumn).value = 'RemainEE'
        ws.cell(row=rowIndex, column=accumulatedLogEEColumn).value = 'AccumulatedLogEE'
        ws.cell(row=rowIndex, column=logEEColumn).value = 'Log EE'
        ws.cell(row=rowIndex, column=idealRemainEEColumn).value = 'IdealRemainEE'
        rowIndex += 1
    
    dateDiff = (datetime.strptime(endDate, '%Y-%m-%d') - datetime.strptime(startDate, '%Y-%m-%d')).days
    # print(dateDiff)
    elapseDay = 0
    elapseWorkingDay = 0
    print('buildTeamBurndownChart', startDate, endDate)
    print('Team name: {}'.format(teamName))
    plotStartRow = 1
    plotEndRow = 1
    workingDays = dateDiff - len(nonWorkingDays[fbId]) + 1
    # print(workingDays)
    startDayRemainEE = teamIssuesContainer[teamName].getRemainEeByDate(startDate)[0] + teamIssuesContainer[teamName].getLogEeByDate(startDate)[0]
    # print('startDayRemainEE: {}'.format(startDayRemainEE))
    ws.cell(row=rowIndex, column=fbColumn).value = fbId
    ws.cell(row=rowIndex, column=weekColumn).value = 'cw' + str(getWeekNumber(startDate))
    ws.cell(row=rowIndex, column=dateColumn).value = 'day0'
    ws.cell(row=rowIndex, column=remainEEColumn).value = startDayRemainEE
    ws.cell(row=rowIndex, column=accumulatedLogEEColumn).value = 0
    ws.cell(row=rowIndex, column=logEEColumn).value = 0
    ws.cell(row=rowIndex, column=idealRemainEEColumn).value = startDayRemainEE
    rowIndex += 1

    for i in range(0, dateDiff+1):
        oneDate = (datetime.strptime(startDate, '%Y-%m-%d') + relativedelta(days=+elapseDay)).strftime('%Y-%m-%d')
        # print(oneDate)
        teamRemainEE = teamIssuesContainer[teamName].getRemainEeByDate(oneDate)[0]
        teamLogEE = teamIssuesContainer[teamName].getLogEeByDate(oneDate)[0]
        teamAccumulatedLogEE = teamIssuesContainer[teamName].getAccumulatedLogEeByDate(oneDate)[0]
        if oneDate not in nonWorkingDays[fbId]:
            # print(oneDate)
            if oneDate <= today:
                plotEndRow += 1
            calendarWeek = 'cw' + str(getWeekNumber(oneDate))
            ws.cell(row=rowIndex, column=fbColumn).value = fbId
            ws.cell(row=rowIndex, column=weekColumn).value = calendarWeek
            ws.cell(row=rowIndex, column=dateColumn).value = oneDate
            if oneDate <= today:
                ws.cell(row=rowIndex, column=remainEEColumn).value = teamRemainEE
                ws.cell(row=rowIndex, column=accumulatedLogEEColumn).value = teamAccumulatedLogEE
                ws.cell(row=rowIndex, column=logEEColumn).value = teamLogEE
            elapseWorkingDay += 1
            ws.cell(row=rowIndex, column=idealRemainEEColumn).value = startDayRemainEE*(1 - elapseWorkingDay/workingDays)
            rowIndex += 1
            
        elapseDay += 1
        

    caChart = LineChart()
    caChart.title = teamName
    caChart.style = 12
    caChart.y_axis.title = "Hours"
    caChart.y_axis.crossAx = 100
    if view == 'Date':
        caChart.x_axis = DateAxis(crossAx=100)
        caChart.x_axis.number_format = 'd-mmm'
        caChart.x_axis.majorTimeUnit = "days"
        caChart.x_axis.title = "Date"
        plotMinColumn = dateColumn
    else:
        caChart.x_axis.title = "Week"
        plotMinColumn = weekColumn
    data = Reference(ws, min_col=remainEEColumn, min_row=plotStartRow, max_col=idealRemainEEColumn, max_row=ws.max_row)
    caChart.add_data(data, titles_from_data=True)
    caChart.series[0].graphicalProperties.line.solidFill = 'FF0000'
    caChart.series[0].marker.symbol = "triangle"
    caChart.series[0].marker.graphicalProperties.line.solidFill = 'FF0000'
    caChart.series[1].graphicalProperties.line.solidFill = '008000'
    caChart.series[1].marker.symbol = "circle"
    caChart.series[1].marker.graphicalProperties.line.solidFill = '008000'
    caChart.series[2].graphicalProperties.line.solidFill = 'FF00FF'
    caChart.series[2].marker.symbol = "star"
    caChart.series[2].marker.graphicalProperties.line.solidFill = 'FF00FF'
    caChart.series[3].graphicalProperties.line.solidFill = '708090'
    caChart.series[3].marker.symbol = "square"
    caChart.series[3].marker.graphicalProperties.line.solidFill = '708090'
    
    catData = Reference(ws, min_col=plotMinColumn, min_row=2, max_row=ws.max_row)
    caChart.set_categories(catData)
    ws._charts.clear()
    ws.add_chart(caChart, 'H3')

issues = genBacklogCAOutput(ws_backlog_CA)

print('Start  build Epic: {}'.format(datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
teamIssuesContainer = buildEpicList(issues)
print('Finish build Epic: {}'.format(datetime.now().strftime('%Y-%m-%d %H:%M:%S')))

for teamName in sorted(teamIssuesContainer):
# for teamName in teamIssuesContainer:
    teamIssues = teamIssuesContainer[teamName]
    if teamName in wbTeamFbBurndown.sheetnames:
        wsTeamBurndown = wbTeamFbBurndown[teamName]
    else:
        wsTeamBurndown = wbTeamFbBurndown.create_sheet(title=teamName)
    buildTeamBurndownChart(teamName, 'Date', fbStartDate, fbEndDate, today, wsTeamBurndown)


snapshotCollectionTime = datetime.now().strftime('%Y%m%d%H%M%S')
wbOutputFile = os.path.join(teamWd,  'team' + '_' + 'FB' + fbId + '_' + 'backlog' + snapshotCollectionTime + '.xlsx')
wb.save(wbOutputFile)
wbTeamFbBurndown.save(teamFbBurndownOutputFile)
print('Total {} sheets'.format(len(wbTeamFbBurndown.sheetnames)))
copy(teamFbBurndownOutputFile, teamFbBurndownOutputFile.split('.')[0]+'_'+datetime.now().strftime('%Y%m%d%H%M%S')+'.xlsx')
print("Elapsed Time: {}".format(datetime.now() - startTime))


print('collection done in {}'.format(wbOutputFile))
print('collection done in {}'.format(teamFbBurndownOutputFile))

powerBiOuputFile = os.path.join(teamWd, 'team' + '_' + 'burndown' + '.xlsx')
copy(teamFbBurndownOutputFile, powerBiOuputFile)
print('Upload to powerBiOuputFile: {}'.format(powerBiOuputFile))